import java.sql.*;

import org.opengauss.Driver;

public class pg_prepared_statement_case0042 {
	public static void main(String[] args) throws SQLException {
		System.out.println(Driver.getGSVersion());
		String url = "jdbc:opengauss://" + args[0] + ":" + args[1] + "/" + args[2];
        String username = args[3];
        String password = args[4];
		String sql;

		System.out.println("----新建连接----");
		Connection conn = GetConnection(username, password, url);

        try {
            System.out.println("----创建存储过程----");
            try {
                sql = "create or replace procedure proc_prepare_42\n" +
                    "(\n" +
                    "	psv_in1 in integer,\n" +
                    "	psv_in2 in integer,\n" +
                    "	psv_inout in out integer\n" +
                    ")\n" +
                    "as\n" +
                    "begin\n" +
                    "	psv_inout := psv_in1 + psv_in2 + psv_inout;\n" +
                    "end;";
                System.out.println(sql);
                PreparedStatement ps = conn.prepareStatement(sql);
                int rs = ps.executeUpdate();
                System.out.println("rs: " + rs);
                ps.close();
            } catch (SQLException e) {
                    System.out.println("ERROR:" + e.getMessage());
                    e.printStackTrace();
            }

            System.out.println("----调用存储过程----");
            try {
                sql = "{? = call proc_prepare_42(?, ?, ?)}";
                CallableStatement cstmt = conn.prepareCall(sql);
                cstmt.setInt(2, 50);
                cstmt.setInt(1, 20);
                cstmt.setInt(3, 90);
                cstmt.registerOutParameter(4, Types.INTEGER);
                cstmt.execute();
                int out = cstmt.getInt(4);
                System.out.println("out: " + out);
                cstmt.close();
            } catch (SQLException e) {
                    System.out.println("ERROR:" + e.getMessage());
                    e.printStackTrace();
            }

            System.out.println("----查询----");
            try {
                sql = "select * from pg_prepared_statement(0);";
                System.out.println(sql) ;
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
			    int cols = rsmd.getColumnCount();
                System.out.println("----查询结果----");
                while (rs.next()) {
                    for(int i=1; i<cols+1; i++) {
                        if (i==cols){
                            System.out.println(rs.getString(i));
                        } else {
                            System.out.print(rs.getString(i) +"|");
                        }
                    }
                }
                ps.close();
            } catch (SQLException e) {
                    System.out.println("ERROR:" + e.getMessage());
                    e.printStackTrace();
            }

            System.out.println("----删除存储过程----");
			sql = "drop procedure if exists proc_prepare_42;";
			Statement stmt = conn.createStatement();
			Boolean rb = stmt.execute(sql);
			System.out.println(rb);
			stmt.close();
        }catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("----关闭连接----");
			if (conn != null) {conn.close();}
		}
	}

	public static Connection GetConnection(String username, String passwd, String sourceURL) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(sourceURL, username, passwd);
			System.out.println(sourceURL);
			System.out.println("Connection succeed");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return conn;
	}
}